package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Position;

public class PositionDao {
	private int pageSize = 9;
	/**
	 * 获取被删除职位
	 * @throws SQLException 
	 */
	public List<Position> getDelJob(int status) throws SQLException {
		String sql = "select * from position where position_status = ? ";
		List<Map<String,Object>>delList=Jdbc.getInstace().executeQueryForList(sql, status);
		return change(delList);
		
	}
	/**
	 * 获取总页数
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(int type) throws SQLException {
		String sql = "select count(*) total from position where position_status = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, type);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	
	/**
	 * 搜索总页数
	 */
	public int getTotalPage(int type, String positionName) throws SQLException {
		String sql = "select count(*) total from position where position_status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, type, positionName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	
	/**
	 * 带条件分页查询
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Position> getPagepositionListByType(int status, int page, String positionName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from position where position_status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, positionName);
		sql += " order by position_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	/**
	 * 修改职位
	 * @throws SQLException 
	 */
	public boolean changePosition(String name,String description,String id) throws SQLException {
		String sql="update position set position_name=?,position_description=? where position_id=?";
		return Jdbc.getInstace().executeUpdate(sql, name,description,id);
	}
	
	/**
	 * 添加职位
	 * @throws SQLException 
	 */
	public boolean addPosition(String name,String description) throws SQLException {
		String sql="insert into position ( position_name, position_description) values (?,?)";
		return Jdbc.getInstace().executeUpdate(sql, name,description);
	}
	
	/**
	 * 查询职位
	 * @throws SQLException 
	 */
	public List<Position> getJobInformation() throws SQLException {
		String sql="select * from position order by position_id desc";
		List<Map<String,Object>>listJob=Jdbc.getInstace().executeQueryForList(sql);
		List<Position>list=change(listJob);
		return list;
	}
	/**
	 * 根据ID删除用户
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteJobById(String id) throws SQLException {
		String sql="update position set position_status=0 where position_id=?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}
	
	/**
	 * 转换List
	 */
	private List<Position> change(List<Map<String,Object>>listJob) {
		List<Position>list=null;
		if (listJob!=null&&listJob.size()>0) {
			list=new ArrayList<>();
			for (Map<String,Object> map : listJob) {
				list.add(new Position(
						Integer.parseInt(map.get("position_id").toString()),
						(String)map.get("position_name"),
						(String)map.get("position_description"),
						Integer.parseInt(map.get("position_status").toString()),
						(String)map.get("position_header")
						));
			}
		}
		return list;
		
	}
	
	private String buildSqlAndParams(List<Object> params, String sql, int status,String positionName) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(positionName)) {
			sql += " and position_name like ? ";
			params.add("%" + positionName + "%");
		}

		return sql;
	}
	
	private List<Position> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Position> positionList = null;
		if (listData != null && listData.size() > 0) {
			positionList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				positionList.add(buildPosition(data));
			}
		}
		return positionList;
	}
	
	private Position buildPosition(Map<String, Object> data) {
		Position position = new Position();
		position.setPositionDescription((String) data.get("position_description"));
		position.setPositionId((Integer) data.get("position_id"));
		position.setPositionName((String) data.get("position_name"));
		position.setPostionStatus((Integer) data.get("position_status"));
		if (data.get("position_id") != null) {
			position.setPositionId(Integer.parseInt(data.get("position_id").toString()) );
		}
		return position;
	}
}
